import os
import sys
import time
import datetime

import pandas as pd

import pymssql
import codecs
import random
import re

import Properties as p
import csv

prop = p.Properties("config.properties").getPropertiesFlat()
server = prop["emas.sms.server"]
user = prop["emas.sms.user"]
password = prop["emas.sms.password"]
database = prop["emas.sms.database"]

server_read = prop["emas.sms.read.server"]
user_read = prop["emas.sms.read.user"]
password_read = prop["emas.sms.read.password"]
database_read = prop["emas.sms.read.database"]


def load_data_from_db(resdir, fn):
    fn_train = resdir + os.sep + fn
    print(fn_train)
    with open(fn_train, 'w', encoding='utf-8', newline='') as csvfile:
        fieldnames = ['eprId', 'ucuid', 'count', 'auditStatus', 'md5content', 'content']
        writer = csv.DictWriter(csvfile, fieldnames=fieldnames, delimiter=",")
        writer.writeheader()

        # 读取昨天的内容，并写到cvs文件
        with pymssql.connect(server_read, user_read, password_read, database_read) as conn:
            with conn.cursor(as_dict=True) as cursor:
                # sql = '''
                # SELECT eprId,ucuid,count(1) as count ,content
                #   FROM EMAS_SMS.dbo.TBL_SMS_CONTENT
                #   where tempid=0
                #     and sendtime>=convert(varchar(10), dateadd(dd,-1,getdate()),120)
                #     and sendtime< convert(varchar(10), getdate(),120)
                #     group by eprid,ucuid,content
                #     order by eprid,ucuid,content
                # '''
                sql = '''
                    select c.eprId,c.ucuid,count(1) as count,a.auditStatus,c.md5content,c.content
                    from EMAS_BASE.dbo.t_sms_audit a with(nolock)
                    inner join EMAS_SMS.dbo.TBL_SMS_CONTENT c with(nolock)
                    on c.MD5CONTENT=a.smsContentMd5
                    where auditTime>=convert(varchar(10), dateadd(dd,-1,getdate()),120)
                                        and auditTime< convert(varchar(10), getdate(),120)
                    group by c.eprId,c.ucuid,c.content ,a.auditStatus,c.md5content
                    order by c.eprId,c.ucuid,c.content ,a.auditStatus,c.md5content
                '''
                #sql = '''
                #        select c.eprId,c.ucuid,count(1) as count,a.auditStatus,c.md5content,c.content
                #                from EMAS_BASE.dbo.t_sms_audit a with(nolock)
                #                left join EMAS_SMS.dbo.TBL_SMS_CONTENT c with(nolock)
                #        on c.MD5CONTENT=a.smsContentMd5
                #        where auditTime>'2022-01-01'
                #        and ucuid in ('500020','500021','500019')
                #        group by c.eprId,c.ucuid,c.content ,c.md5content,a.auditStatus
                #        order by c.eprId,c.ucuid,c.content ,c.md5content,a.auditStatus
                #'''
                #sql = '''
                #                select c.eprId,c.ucuid,count(1) as count,a.auditStatus,c.md5content,c.content
                #                from EMAS_BASE.dbo.t_sms_audit a with(nolock)
                #                inner join EMAS_SMS_CACHE.dbo.TBL_SMS_CONTENT_CACHE c with(nolock)
                #                on c.MD5CONTENT=a.smsContentMd5
                #                where auditTime>'2022-01-01'
                #                and ucuid in ('500020','500021','500019')
                #                group by c.eprId,c.ucuid,c.content ,c.md5content,a.auditStatus
                #                order by c.eprId,c.ucuid,c.content ,c.md5content,a.auditStatus
                #'''

                print(sql)
                cursor.execute(sql)
                for row in cursor:
                    # content = row['content']
                    # eprId = row['eprId']
                    # ucuid = row['ucuid']
                    row['content'] = re.sub(r"[\r\n]+", "***", row['content'])
                    writer.writerow(row)


def load_rs_txt(resdir, fn):
    """
    加载结果文件，并入库
    :param resdir:
    :param fn:
    :return:
    """
    train_data = pd.read_table(resdir + os.sep + fn, header=None, error_bad_lines=False,
                               skiprows=0)
    train_data.columns = ['eprId', 'uid', 'auditStatus', 'md5content', 'text']
    count_doc = 0
    all_doc_list = []
    for index, row in train_data.iterrows():
        rowstr = row['text']
        if not (isinstance(rowstr, str) and len(rowstr) > 0):
            print("错误" + str(rowstr))
            continue
        # rowstr = rowstr.replace("\t", "").replace("\n", "")
        eprId = str(row['eprId'])
        uid = str(row['uid'])
        auditStatus = str(row['auditStatus'])
        md5content = str(row['md5content'])
        content = str(row['text'])
        count_doc = count_doc + 1

        # 判断是否同一个账号的内容和签名
        all_doc_list.append((eprId, uid, auditStatus, md5content, content))

        if len(all_doc_list) >= 100:
            # print("count:"+str(count))
            yield all_doc_list
            all_doc_list = []

    # 最后一批
    if len(all_doc_list) > 0:
        yield all_doc_list


def save_to_db(resdir, rs3_txt):
    # 读取昨天的内容，并写到cvs文件
    with pymssql.connect(server, user, password, database, autocommit=True) as conn:
        with conn.cursor(as_dict=True) as cursor:
            sql = """
                    insert into EMAS_PUBLIC.dbo.audit_template 
                      (eprId,uid,templateId,auditStatus,md5content,content,admin,updateTime,createTime)
                      values (%d,%s,0,%d,%s,%s,'auto',null,getdate())
                """
            print(sql)
            for list_tmp in load_rs_txt(resdir, rs3_txt):
                t1 = get_current_timestamp()  # 毫秒级时间戳
                cursor.executemany(sql, list_tmp)
                t2 = get_current_timestamp()  # 毫秒级时间戳
                print("insert times:" + str(t2 - t1) + ",list.size:" + str(len(list_tmp)))


def get_current_timestamp():
    """
    :return:  获取当明时间戳(毫秒)
    """
    return int(round(time.time() * 1000))


if __name__ == '__main__':
    try:
        # load_data_from_db(".","test.cvs")
        # list_tmp = [
        #     (80, "chenss",1, "test1"),
        #     (80, "chenss",1, "test2"),
        #     (80, "chenss",1, "test3")
        # ]
        save_to_db(".", "rs3-2.txt")
    except Exception as e:
        print(e)
    # print(int(round(time.time() * 1000)))  # 毫秒级时间戳
